﻿// Tierless
// Copyright © 2010 Alphora
// This file is licensed under a modified BSD-license (see tierless_license.txt).

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

using Tierless.Infrastructure.Data;
using Tierless.Framework.Data;
using Tierless.Framework.Entities;
using Tierless.Framework.EntityTypes;
using Tierless.Framework.Constraints;
using Tierless.Framework.Constraints.Common;
using Tierless.Framework.Utilities;
using Tierless.Framework.Server.Data;
using Tierless.Framework.Server.Constraints;

namespace Tierless.Framework.Server.Data
{
	/// <summary>
	/// Provides access to entity types for the entity model.
	/// </summary>
	public class EntityTypeProvider : IServerEntityTypeProvider
	{
		/// <summary>
		/// Initializes a new instance of the EntityTypeProvider class.
		/// </summary>
		/// <param name="database">The database to use for the entity store.</param>
		public EntityTypeProvider(Database database)
		{
			_database = database;
		}
		
		private class EntityTypeCacheItem
		{
			public EntityTypeCacheItem(string entityTypeName)
			{
				EntityTypeName = entityTypeName;
			}

			public string EntityTypeName { get; private set; }

			public EntityType EntityType { get; set; }

			public ServerEntityType ServerEntityType { get; set; }
		}
		
		private Dictionary<string, EntityTypeCacheItem> _entityTypeCache = new Dictionary<string, EntityTypeCacheItem>();
		
		private object _entityTypeCacheSync = new object();

		private Database _database;
		protected Database Database
		{
			get { return _database; }
		}
		
		// NOTE: This default is specific to MSSQLServer
		private static string _defaultSchemaName = "dbo";
		/// <summary>
		/// Gets or sets the default schema name.
		/// </summary>
		/// <remarks>
		/// Note: The default for this property is 'dbo' which is specific to Microsoft
		/// SQL Server.
		/// </remarks>
		public static string DefaultSchemaName
		{
			get { return _defaultSchemaName; }
			set { _defaultSchemaName = value; }
		}

		protected virtual string GetEntityColumnTypeName(string typeName)
		{
			switch (typeName.ToLower())
			{
				case "bigint" : return "Tierless.Framework.Entities.Int64Column,Tierless.Framework";
				case "binary" : return "Tierless.Framework.Entities.ByteArrayColumn,Tierless.Framework";
				case "bit" : return "Tierless.Framework.Entities.BooleanColumn,Tierless.Framework";
				case "char" : return "Tierless.Framework.Entities.StringColumn,Tierless.Framework";
				case "datetime" : return "Tierless.Framework.Entities.DateTimeColumn,Tierless.Framework";
				case "decimal" : return "Tierless.Framework.Entities.DecimalColumn,Tierless.Framework";
				case "float" : return "Tierless.Framework.Entities.FloatColumn,Tierless.Framework";
				case "image" : return "Tierless.Framework.Entities.ByteArrayColumn,Tierless.Framework";
				case "int" : return "Tierless.Framework.Entities.Int32Column,Tierless.Framework";
				case "money" : return "Tierless.Framework.Entities.DecimalColumn,Tierless.Framework";
				case "nchar" : return "Tierless.Framework.Entities.StringColumn,Tierless.Framework";
				case "ntext" : return "Tierless.Framework.Entities.StringColumn,Tierless.Framework";
				case "numeric" : return "Tierless.Framework.Entities.DecimalColumn,Tierless.Framework";
				case "nvarchar" : return "Tierless.Framework.Entities.StringColumn,Tierless.Framework";
				case "real" : return "Tierless.Framework.Entities.FloatColumn,Tierless.Framework";
				case "smalldatetime" : return "Tierless.Framework.Entities.DateTimeColumn,Tierless.Framework";
				case "smallint" : return "Tierless.Framework.Entities.Int16Column,Tierless.Framework";
				case "smallmoney" : return "Tierless.Framework.Entities.DecimalColumn,Tierless.Framework";
				case "sql_variant" : throw new NotSupportedException("Columns of type 'sql_variant' are not supported.");
				case "sysname" : return "Tierless.Framework.Entities.StringColumn,Tierless.Framework";
				case "text" : return "Tierless.Framework.Entities.StringColumn,Tierless.Framework";
				case "timestamp" : return "Tierless.Framework.Entities.ByteArrayColumn,Tierless.Framework";
				case "tinyint" : return "Tierless.Framework.Entities.ByteColumn,Tierless.Framework";
				case "uniqueidentifier" : return "Tierless.Framework.Entities.GuidColumn,Tierless.Framework";
				case "varbinary" : return "Tierless.Framework.Entities.ByteArrayColumn,Tierless.Framework";
				case "varchar" : return "Tierless.Framework.Entities.StringColumn,Tierless.Framework";
				case "xml" : throw new NotSupportedException("Columns of type 'xml' are not supported.");
				default : throw new NotSupportedException(String.Format("Columns of type '{0}' are not supported.", typeName));
			}
		}
		
		protected virtual string GetNativeTypeName(string typeName)
		{
			switch (typeName.ToLower())
			{
				case "bigint" : return "System.Int64";
				case "binary" : return "System.Byte[]";
				case "bit" : return "System.Boolean";
				case "char" : return "System.String";
				case "datetime" : return "System.DateTime";
				case "decimal" : return "System.Decimal";
				case "float" : return "System.Double";
				case "image" : return "System.Byte[]";
				case "int" : return "System.Int32";
				case "money" : return "System.Decimal";
				case "nchar" : return "System.String";
				case "ntext" : return "System.String";
				case "numeric" : return "System.Decimal";
				case "nvarchar" : return "System.String";
				case "real" : return "System.Double";
				case "smalldatetime" : return "System.DateTime";
				case "smallint" : return "System.Int16";
				case "smallmoney" : return "System.Decimal";
				case "sql_variant" : throw new NotSupportedException("Columns of type 'sql_variant' are not supported.");
				case "sysname" : return "System.String";
				case "text" : return "System.String";
				case "timestamp" : return "System.Byte[]";
				case "tinyint" : return "System.Byte";
				case "uniqueidentifier" : return "System.Guid";
				case "varbinary" : return "System.Byte[]";
				case "varchar" : return "System.String";
				case "xml" : throw new NotSupportedException("Columns of type 'xml' are not supported.");
				default : throw new NotSupportedException(String.Format("Columns of type '{0}' are not supported.", typeName));
			}
		}
		
		protected virtual void InferReferences(ServerEntityType serverEntityType, EntityType entityType)
		{
			// NOTE: I tried to use the INFORMATION_SCHEMA views for this, but they do not contain sequence information for foreign key columns.
			// Seems like an astonishing oversight for the SQL-92 committee to have made, but there you have it.
			// As a result, this portion of the framework is specific to SQL-Server 2005.
			// TODO: Introduce a view 'Tierless_References', that if it exists, would be used instead of this. I didn't do that
			// for now, because I don't want to force the database to have anything in it in order to use the Tierless Framework.
            using 
            (
				var reader = Database.Open
				(
					@"
						select 
								fk.name ReferenceName, 
								ss.name SourceSchemaName,
								st.name SourceTableName,
								sc.name SourceColumnName,
								ts.name TargetSchemaName,
								tt.name TargetTableName,
								tc.name TargetColumnName,
								fkc.constraint_column_id Sequence
							from sys.foreign_keys fk
								join sys.tables st on fk.parent_object_id = st.object_id
								join sys.schemas ss on st.schema_id = ss.schema_id
								join sys.tables tt on fk.referenced_object_id = tt.object_id
								join sys.schemas ts on tt.schema_id = ts.schema_id
								join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
								join sys.columns sc on fkc.parent_object_id = sc.object_id and fkc.parent_column_id = sc.column_id
								join sys.columns tc on fkc.referenced_object_id = tc.object_id and fkc.referenced_column_id = tc.column_id
							where (ss.name = IsNull(@SchemaName, ss.name) and st.name = @TableName)
								or (ts.name = IsNull(@SchemaName, ts.name) and tt.name = @TableName)
							order by fk.name, fkc.constraint_column_id
					"
					, 
					new Param("@SchemaName", serverEntityType.SchemaName),
					new Param("@TableName", serverEntityType.TableName)
				)
			)
			{
				string referenceName = null;
				EntityReference reference = null;
				
				while (reader.Read())
				{
					if ((referenceName ?? "") != Convert.ToString(reader[0]))
					{
						if ((reference != null) && !entityType.References.Contains(referenceName))
							entityType.References.Add(reference);
							
						referenceName = Convert.ToString(reader[0]);
						reference = new EntityReference(referenceName);
						
						var sourceSchemaName = Convert.ToString(reader[1]);
						var sourceTableName = Convert.ToString(reader[2]);
						var targetSchemaName = Convert.ToString(reader[4]);
						var targetTableName = Convert.ToString(reader[5]);
						
						reference.SourceEntityTypeName = 
							sourceSchemaName == _defaultSchemaName 
								? sourceTableName 
								: String.Format("{0}.{1}", sourceSchemaName, sourceTableName);
						
						reference.TargetEntityTypeName = 
							targetSchemaName == _defaultSchemaName 
								? targetTableName 
								: String.Format("{0}.{1}", targetSchemaName, targetTableName);
							
						reference.Kind = 
							reference.SourceEntityTypeName == serverEntityType.Name 
								? ReferenceKind.Lookup 
								: ReferenceKind.Child;
					}
					
					reference.SourceColumns.Add(new EntityTypeColumnReference(Convert.ToString(reader[3])));
					reference.TargetColumns.Add(new EntityTypeColumnReference(Convert.ToString(reader[6])));
					
					// TODO: Referential actions?
				}
				
				if ((reference != null) && !entityType.References.Contains(referenceName))
					entityType.References.Add(reference);
			}
		}
		
		protected virtual void InferConstraints(ServerEntityType serverEntityType, EntityType entityType)
		{
			// Auto-registration of key constraints, and reference constraints, both regular and temporal.
			// Under the assumption that these are the only constraints being auto-registered, the only constraints that
			// can possibly be violated by a change to an entity of the given type are the ones that directly involve that
			// entity type as the source or target of the reference in the case of standard and temporal reference constraints. 
			// Therefore, the type graph traversal only needs to identify lookups and collections of the entity for the given entity
			// type.
			
			// Temporal constraints
			if (entityType.IsTemporal)
			{
				var constraint = new DateRangeValid(entityType.Name, entityType.FromDateColumnName, entityType.ToDateColumnName);
				if (!entityType.Constraints.Contains(constraint.Name))
					entityType.Constraints.Add(constraint);
			}
			
			// Key constraints
			foreach (var key in entityType.Keys)
			{
				var constraint = new KeyConstraint(serverEntityType, key);
				if (!serverEntityType.Constraints.Contains(constraint.Name))
					serverEntityType.Constraints.Add(constraint);
			}
			
			// Reference constraints
			foreach (var reference in entityType.References)
			{
				if (reference.Kind == ReferenceKind.Lookup)
				{	
					var targetServerEntityType = GetServerEntityType(reference.TargetEntityTypeName);
					var targetEntityType = GetEntityType(reference.TargetEntityTypeName);
					
					if (entityType.IsTemporal && targetEntityType.IsTemporal)
					{
						var constraint = 
							new SourceTemporalReference
							(
								reference,
								serverEntityType,
								targetServerEntityType
							);

						if (!serverEntityType.Constraints.Contains(constraint.Name))
							serverEntityType.Constraints.Add(constraint);
					}
					else
					{
						var constraint = 
							new SourceReference
							(
								entityType.Name, 
								reference.SourceColumns.Select(c => c.Name).ToArray(), 
								reference.TargetEntityTypeName, 
								reference.TargetColumns.Select(c => c.Name).ToArray()
							);

						if (!serverEntityType.Constraints.Contains(constraint.Name))
							serverEntityType.Constraints.Add(constraint);
					}
				}
				else
				{
					var sourceServerEntityType = GetServerEntityType(reference.SourceEntityTypeName);
					var sourceEntityType = GetEntityType(reference.SourceEntityTypeName);

					// Target referential action
					switch (reference.ReferentialAction)
					{
						case ReferentialAction.Require :
							if (entityType.IsTemporal && sourceServerEntityType.IsTemporal)
							{
								var constraint =
									new TargetTemporalReference
									(
										reference,
										sourceServerEntityType,
										serverEntityType
									);
								
								if (!serverEntityType.Constraints.Contains(constraint.Name))
									serverEntityType.Constraints.Add(constraint);
							}
							else
							{
								// TargetReference
								var constraint =
									new TargetReference
									(
										entityType.Name,
										reference.TargetColumns.Select(c => c.Name).ToArray(),
										reference.SourceEntityTypeName,
										reference.SourceColumns.Select(c => c.Name).ToArray()
									);

								if (!serverEntityType.Constraints.Contains(constraint.Name))
									serverEntityType.Constraints.Add(constraint);
							}
						break;
						
						case ReferentialAction.Cascade :
						case ReferentialAction.Clear :
							serverEntityType.EventHandlers.Add(new ReferentialActionHandler(reference, ReferentialActionTrigger.Update));
							serverEntityType.EventHandlers.Add(new ReferentialActionHandler(reference, ReferentialActionTrigger.Delete));
						break;
					}
				}
			}
		}
		
		protected virtual void InferEntityTypeFromStructure(ServerEntityType serverEntityType, EntityType entityType)
		{
			// Columns
			var defaultTypes = EntityTypeFactory.GetEntityDefaultTypes(entityType.Name);
			var defaultValues = EntityTypeFactory.GetEntityDefaultValues(entityType.Name);
			
			var primaryKey = new EntityKey(String.Format("PK_{0}", entityType.Name)) { IsPrimary = true };
			
			foreach (DataRow row in Database.DescribeStatement(String.Format("select * from {0}", GetEntityFullName(serverEntityType))).Rows)
			{
				var columnName = row.Field<string>("ColumnName");
				EntityTypeColumn columnType;
				var columnTypeIndex = entityType.ColumnTypes.IndexOf(columnName);
				if (columnTypeIndex < 0)
				{
					columnType = new EntityTypeColumn(columnName);

					if (row.Field<bool?>("IsIdentity").GetValueOrDefault())
						serverEntityType.IdentityColumnName = columnName;
						
					if (row.Field<bool?>("IsKey").GetValueOrDefault())
					{
						primaryKey.Columns.Add(new EntityTypeColumnReference(columnType.Name));
						columnType.IsKey = true;
					}
						
					columnType.Title = columnType.Name;
					columnType.EntityColumnTypeName = GetEntityColumnTypeName(row.Field<string>("DataTypeName"));
					columnType.NativeTypeName = GetNativeTypeName(row.Field<string>("DataTypeName"));
					columnType.MaxLength = row.Field<int?>("ColumnSize").GetValueOrDefault();
					columnType.Precision = row.Field<short?>("NumericPrecision").GetValueOrDefault();
					// HACK: SQL Server money reports 19:255 for precision and scale rather than 19:4
					if (row.Field<string>("DataTypeName") == "money")
						columnType.Scale = 4;
					else
						columnType.Scale = row.Field<short?>("NumericScale").GetValueOrDefault();
					columnType.IsRequired = !row.Field<bool?>("AllowDBNull").GetValueOrDefault();

					entityType.ColumnTypes.Add(columnType);
				}
				else
					columnType = entityType.ColumnTypes[columnTypeIndex];
					
				// Temporal columns (FromDate and ToDate)
				if ((columnName == serverEntityType.FromDateColumnName) && columnType.IsKey)
					entityType.FromDateColumnName = columnName;
					
				if (columnName == serverEntityType.ToDateColumnName)
					entityType.ToDateColumnName = columnName;
				
				// Column default
				if (columnType.Default == null)
				{
					EntityTypeColumnDefault defaultDef;
					if (defaultTypes.TryGetValue(columnType.Name, out defaultDef))
						columnType.Default = defaultDef;
					else
					{
						if (columnType.DefaultValue == null)
						{
							object defaultValue;
							if (defaultValues.TryGetValue(columnType.Name, out defaultValue))
								columnType.DefaultValue = defaultValue;
						}
					}
				}
				
				// FromDate default for temporal entities
				if ((columnType.Name == entityType.FromDateColumnName) && (columnType.Default == null) && (columnType.DefaultValue == null))
					columnType.Default = new TodayDefault();
				
				// Required constraint -- Don't add to an identity column that is generated in the database.
				if (columnType.IsRequired && (columnType.Name != serverEntityType.IdentityColumnName))
				{
					EntityConstraint notNullConstraint;
					if (columnType.NativeTypeName == "System.String")
						notNullConstraint = new NotNullOrEmpty(entityType.Name, columnType.Name, columnType.Title);
					else
						notNullConstraint = new NotNull(entityType.Name, columnType.Name, columnType.Title);
						
					if (!entityType.Constraints.Contains(notNullConstraint.Name))
						entityType.Constraints.Add(notNullConstraint);
				}
				
				// String max-length constraint
				if ((columnType.NativeTypeName == "System.String") && (columnType.MaxLength >= 0)) // (max) columns have -1 as the length specifier
				{
					var stringLengthValid = new StringLengthValid(entityType.Name, columnType.Name, columnType.MaxLength);
					if (!entityType.Constraints.Contains(stringLengthValid))
						entityType.Constraints.Add(stringLengthValid);
				}
				
				// Decimal value in range
				if (columnType.NativeTypeName == "System.Decimal")
				{
					var decimalValueValid = new DecimalValueValid(entityType.Name, columnType.Name, columnType.Precision, columnType.Scale);
					if (!entityType.Constraints.Contains(decimalValueValid))
						entityType.Constraints.Add(decimalValueValid);
				}
				
				// DateTime in range constraint
				if (columnType.NativeTypeName == "System.DateTime")
				{
					var dateTimeInRange = new DateTimeInRange(entityType.Name, columnType.Name);
					if (!entityType.Constraints.Contains(dateTimeInRange))
						entityType.Constraints.Add(dateTimeInRange);
				}
			}
			
			if ((entityType.FromDateColumnName != null) && (entityType.ToDateColumnName != null))
			{
				serverEntityType.IsTemporal = true;
				entityType.IsTemporal = true;
				primaryKey.Columns.RemoveAt(primaryKey.Columns.IndexOf(entityType.FromDateColumnName));
				primaryKey.IsTemporal = true;
			}
			
			// TODO: Infer Additional Keys
			entityType.Keys.Add(primaryKey);
			
			// Command support
			serverEntityType.SupportsInsert = true;
			serverEntityType.SupportsUpdate = entityType.PrimaryKey != null;
			serverEntityType.SupportsDelete = entityType.PrimaryKey != null;
			
			// References
			InferReferences(serverEntityType, entityType);
			
			// Infer a default traversal that will bring in all lookup entities.
			if (entityType.DefaultTraversal == null)
			{
				var defaultRelatedEntityTypeNames = new List<string>();
				foreach (var reference in entityType.References)
					if ((reference.Kind == ReferenceKind.Lookup) && !defaultRelatedEntityTypeNames.Contains(reference.TargetEntityTypeName))
						defaultRelatedEntityTypeNames.Add(reference.TargetEntityTypeName);
						
				if (defaultRelatedEntityTypeNames.Count > 0)
					entityType.DefaultTraversal = new EntityTraversal(EntityTraversalType.Immediate, defaultRelatedEntityTypeNames.ToArray());
			}

			// Infer constraints
			InferConstraints(serverEntityType, entityType);

			// TODO: Infer Orders
		}

		protected virtual void InferEntityTypeFromSchemaTable(ServerEntityType serverEntityType, EntityType entityType, DataTable schemaTable)
		{
			foreach (DataRow row in schemaTable.Rows)
			{
				var columnName = row.Field<string>("ColumnName");
				if (!entityType.ColumnTypes.Contains(columnName))
				{
					var columnType = new EntityTypeColumn(columnName);
					columnType.Title = columnType.Name;
					columnType.ColumnType = EntityColumnType.Stored;
					columnType.EntityColumnTypeName = GetEntityColumnTypeName(row.Field<string>("DataTypeName"));
					columnType.NativeTypeName = GetNativeTypeName(row.Field<string>("DataTypeName"));
					columnType.MaxLength = row.Field<int?>("ColumnSize").GetValueOrDefault();
					columnType.IsRequired = false;
					entityType.ColumnTypes.Add(columnType);
				}
			}
		}
		
		protected virtual void InferCoreSelectStatement(ServerEntityType serverEntityType, EntityType entityType, StringBuilder statement)
		{
			// Build the select clause
			statement.Append("select\r\n");
			
			var firstColumn = true;
			foreach (var columnType in entityType.ColumnTypes.Where(AColumnType => AColumnType.IsStored))
			{
				if (!firstColumn)
					statement.Append(",\r\n");
				else
					firstColumn = false;
					
				statement.AppendFormat
				(
					"\t\t{0}.{1} {2}", 
					"T",
					columnType.Name,
					columnType.Name
				);
			}
			statement.Append("\r\n");
			
			// Build the standard from clause
			statement.AppendFormat("\tfrom {0} T /*%tablehint%*/\r\n", GetEntityFullName(serverEntityType));
			
			// Build the where clause with the keycondition placeholder
			statement.Append("\twhere (/*%filtercondition%*/)\r\n");
			
			// Build the where clause to limit the result set to data as of the AsOf date
			if (entityType.IsTemporal)
			{
				statement.AppendFormat
				(
					"\t\tand (@{0} between T.{0} and IsNull(T.{1}, @{0}))\r\n", // and (@FromDate between T.FromDate and IsNull(T.ToDate, @FromDate))
					entityType.FromDateColumnName, 
					entityType.ToDateColumnName
				);
			}
		}

		// TODO: This method assumes the SQL Server qualifier.
		/// <summary>
		/// Returns the fully qualified database object name for the given server entity type.
		/// </summary>
		/// <param name="entityType">The name of the entity type.</param>
		/// <returns>The fully qualified database object name.</returns>
		public static string GetEntityFullName(ServerEntityType entityType)
		{
			if (String.IsNullOrEmpty(entityType.SchemaName))
				return "[" + entityType.TableName + "]";
			else
				return "[" + entityType.SchemaName + "].[" + entityType.TableName + "]";
		}
		
		protected virtual void InferSelectCommand(ServerEntityType serverEntityType, EntityType entityType)
		{
			var commandType = serverEntityType.SelectCommand;
			
			var statement = new StringBuilder();
			
			InferCoreSelectStatement(serverEntityType, entityType, statement);
			
			commandType.Statement = statement.ToString();
		}
		
		protected virtual void InferInsertCommand(ServerEntityType serverEntityType, EntityType entityType)
		{
			var command = serverEntityType.InsertCommand;

			// Build the column lists
			var valuesList = new StringBuilder();
			var columnList = new StringBuilder();
			foreach (var columnType in entityType.ColumnTypes)
			{
				if (columnType.IsStored && (columnType.Name != serverEntityType.IdentityColumnName))
				{
					if (columnList.Length > 0)
					{
						columnList.Append(", ");
						valuesList.Append(", ");
					}

					columnList.AppendFormat("{0}", columnType.Name);
					valuesList.AppendFormat("@{0}", columnType.Name);
				}
			}
			
			// Build the main insert statement
			command.Statement = 
				String.Format
				(
					"{0}"
						+ "insert\r\n"
						+ "\tinto {1} ({2})\r\n"
						+ "{3}"
						+ "\tvalues ({4})\r\n"
						+ "{5}",
					serverEntityType.HasIdentityColumn 
						? ("declare @outtemp table ( ID int )\r\n") // NOTE: This is assuming integer identity columns, because we don't have the SQL data type name anywhere
						: "",
					GetEntityFullName(serverEntityType),
					columnList,
					serverEntityType.HasIdentityColumn 
						? ("\toutput inserted." + serverEntityType.IdentityColumnName + " into @outtemp\r\n") 
						: "",
					valuesList,
					serverEntityType.HasIdentityColumn
						? ("\tselect @" + serverEntityType.IdentityColumnName + " = ID from @outtemp\r\n")
						: ""
				);
		}

		protected virtual void InferUpdateCommand(ServerEntityType serverEntityType, EntityType entityType)
		{
			if (entityType.PrimaryKey == null)
				throw new InvalidOperationException(String.Format("Update command cannot be inferred for entity '{0}' because it does not have a primary key defined.", entityType.Name));

			var commandDef = serverEntityType.UpdateCommand;
			
			// Build the key condition
			var keyCondition = new StringBuilder();
			if (entityType.PrimaryKey != null)
				foreach (var keyColumn in entityType.PrimaryKey.Columns)
				{
					if (keyCondition.Length != 0)
						keyCondition.AppendFormat(" and ");
					keyCondition.AppendFormat("{0} = @Old_{0}", keyColumn.Name);
				}

			if (keyCondition.Length == 0)
				keyCondition.Append("1 = 1");
			
			// Build the set clause
			var setClause = new StringBuilder();
			foreach (var columnType in entityType.ColumnTypes)
			{
				// TODO: Mutable IDs
				if 
				(
					columnType.IsStored 
						&& columnType.Name != serverEntityType.IdentityColumnName 
						&& entityType.PrimaryKey != null 
						&& !entityType.PrimaryKey.Columns.Contains(columnType.Name)
				)
				{
					if (setClause.Length > 0)
						setClause.AppendFormat(",\r\n");
						
					setClause.AppendFormat("\t\t{0} = @{0}", columnType.Name);
				}
			}
			
			// Build the concurrency condition
			var concurrencyCondition = new StringBuilder();
			switch (serverEntityType.ConcurrencyMode)
			{
				// TODO: ModifiedOnly optimistic concurrency
				case OptimisticConcurrencyMode.ModifiedOnly : 
					throw new NotImplementedException("The modified-only concurrency mode is not yet implemented. Use All or KeyOnly.");

				case OptimisticConcurrencyMode.All : 
				{
					foreach (var columnType in entityType.ColumnTypes)
					{
						if 
						(
							columnType.IsStored 
								&& columnType.Name != serverEntityType.IdentityColumnName 
								&& !entityType.PrimaryKey.Columns.Contains(columnType.Name) 
								&& columnType.IsComparable
						)
						{
							if (concurrencyCondition.Length != 0)
								concurrencyCondition.AppendFormat(" and ");
							concurrencyCondition.AppendFormat("{0} = @Old_{0}", columnType.Name);
						}
					}
					
					break;
				}
			}

			// Build the update statement
			
			var statement = new StringBuilder();
			
			//	%IsTemporal%
			//		if @Old_ToDate is not null and (@ThroughDate is null or @ThroughDate <> @Old_ToDate)
			//		begin
			//			if @ThroughDate is not null
			//				update T set FromDate = @ThroughDate + 1 where %keycondition% and FromDate = (select Max(FromDate) from T where %keycondition% and FromDate <= @ThroughDate)
			//			delete T where %keycondition% and FromDate > @Old_ToDate and (@ThroughDate is null or ToDate < @ThroughDate)
			//		end
			//		
			//		if @Old_ToDate is null and @ThroughDate is not null
			//		begin
			//			insert into T select %olddata%, @ThroughDate + 1 FromDate from T where %keycondition% and FromDate = @Old_FromDate
			//		end
			//
			//		if @AsOf <= @Old_FromDate
			//		begin
			//			if @AsOf < @Old_FromDate
			//			begin
			//				delete T where %keycondition% and FromDate >= @AsOf and ToDate < @Old_FromDate
			//				update T set ToDate = @AsOf - 1 where %keycondition% and FromDate = (select Max(FromDate) from T where %keycondition% and FromDate < @AsOf)
			//			end
			//	%EndIsTemporal%
			//			update T set %newdata% where %keycondition% and FromDate = @Old_FromDate and %concurrencycondition%
			//			set @RowsAffected = @@ROWCOUNT
			//	%IsTemporal%
			//		end
			//		else
			//		begin
			//			update T set ToDate = @AsOf - 1 where %keycondition% and FromDate = @Old_FromDate and %concurrencycondition%
			//			set @RowsAffected = @@ROWCOUNT
			//			insert into T values ( %newdata% )
			//		end
			//	%EndIsTemporal%
			
			var insertList = entityType.ColumnTypes.Where(c => c.IsStored && c.Name != serverEntityType.IdentityColumnName);
			
			// TODO: Temporal granularity { Day, Hour, Minute, Second, Millisecond }
			if (entityType.IsTemporal)
			{
				statement.AppendFormat
				(
					"if @Old_{3} is not null and (@{3} is null or @{3} <> @Old_{3})\r\n" +
					"begin\r\n" +
					"\tif @{3} is not null\r\n" +
					"\t\tupdate {0} set {2} = DateAdd(day, 1, @{3})\r\n" +
					"\t\t\twhere {1} and {2} = (select Max({2}) from {0} where {1} and {2} <= @{3})\r\n" +
					"\tdelete {0] where {1} and {2} > @Old_{3} and (@{3} is null or {3} < @{3})\r\n" +
					"end\r\n" +
					"\r\n" +
					"if @Old_{3} is null and @{3} is not null\r\n" +
					"begin\r\n" +
					"\tinsert\r\n" +
					"\t\tinto {0} ({4})\r\n" +
					"\t\tselect {5} from {0} where {1} and {2} = @Old_{2}\r\n" +
					"end\r\n" +
					"\r\n" +
					"if @{2} <= @Old_{2}\r\n" +
					"begin\r\n" +
					"\tif @{2} < @Old_{2}\r\n" +
					"\tbegin\r\n" +
					"\t\tdelete {0} where {1} and {2} >= @{2} and {3} < @Old_{2}\r\n" +
					"\t\tupdate {0} set {3} = DateAdd(day, -1, @{2}) where {1} and {2} = (select Max({2}) from {0} where {1} and {2} < @{2})\r\n" +
					"\tend\r\n",
					GetEntityFullName(serverEntityType),
					keyCondition,
					entityType.FromDateColumnName,
					entityType.ToDateColumnName,
					insertList.Select(c => c.Name).Combine(", "),
					insertList.Select(c => c.Name == entityType.FromDateColumnName ? "DateAdd(day, 1, @" + entityType.ToDateColumnName + ")" : c.Name).Combine(", ")
				);
			}
			
			statement.AppendFormat
			(
				"\tupdate {0} set {1} where {2}{3}{4}\r\n" +
				"\tset @RowsAffected = @@ROWCOUNT\r\n",
				GetEntityFullName(serverEntityType),
				setClause,
				keyCondition,
				entityType.IsTemporal ? String.Format(" and {0} = @Old_{0}", entityType.FromDateColumnName) : "",
				concurrencyCondition.Length > 0 ? " and " + concurrencyCondition : ""
			);
				
			if (entityType.IsTemporal)
			{
				statement.AppendFormat
				(
					"end\r\n" +
					"else\r\n" +
					"begin\r\n" +
					"\tupdate {0} set {3} = DateAdd(day, -1, @{2}) where {1} and {2} = @Old_{2}{4}\r\n" +
					"\tset @RowsAffected = @@ROWCOUNT\r\n" +
					"\t{5}\r\n" +
					"end\r\n",
					GetEntityFullName(serverEntityType),
					keyCondition,
					entityType.FromDateColumnName,
					entityType.ToDateColumnName,
					concurrencyCondition.Length > 0 ? " and " + concurrencyCondition : "",
					serverEntityType.InsertCommand.Statement
				);
			}
			
			commandDef.Statement = statement.ToString();
		}

		protected virtual void InferDeleteCommand(ServerEntityType serverEntityType, EntityType entityType)
		{
			if (entityType.PrimaryKey == null)
				throw new InvalidOperationException(String.Format("Delete command cannot be inferred for entity '{0}' because it does not have a primary key defined.", entityType.Name));

			var commandDef = serverEntityType.DeleteCommand;
			
			var statement = new StringBuilder();
			
			// Build the key condition
			var keyCondition = new StringBuilder();
			if (entityType.PrimaryKey != null)
				foreach (var keyColumn in entityType.PrimaryKey.Columns)
				{
					if (keyCondition.Length != 0)
						keyCondition.AppendFormat(" and ");
					keyCondition.AppendFormat("{0} = @Old_{0}", keyColumn.Name);
				}

			if (keyCondition.Length == 0)
				keyCondition.Append("1 = 1");
			
			// Build the concurrency condition
			var concurrencyCondition = new StringBuilder();
			switch (serverEntityType.ConcurrencyMode)
			{
				// TODO: ModifiedOnly optimistic concurrency
				case OptimisticConcurrencyMode.ModifiedOnly : 
					throw new NotImplementedException("The modified only concurrency mechanism is not yet implemented. Use All or KeyOnly.");

				case OptimisticConcurrencyMode.All : 
				{
					foreach (var columnType in entityType.ColumnTypes)
					{
						if 
						(
							columnType.IsStored 
								&& columnType.Name != serverEntityType.IdentityColumnName 
								&& !entityType.PrimaryKey.Columns.Contains(columnType.Name) 
								&& columnType.IsComparable
						)
						{
							if (concurrencyCondition.Length != 0)
								concurrencyCondition.AppendFormat(" and ");
							concurrencyCondition.AppendFormat("{0} = @Old_{0}", columnType.Name);
						}
					}
					
					break;
				}
			}

			// Build the delete statement

			//	%IsTemporal%
			//		if @Old_ToDate is not null and (@ThroughDate is null or @ThroughDate <> @Old_ToDate)
			//		begin
			//			if @ThroughDate is not null
			//				update T set FromDate = @ThroughDate + 1 where %keycondition% and FromDate = (select Max(FromDate) from T where %keycondition% and FromDate <= @ThroughDate)
			//			delete T where %keycondition% and FromDate > @Old_ToDate and (@ThroughDate is null or ToDate < @ThroughDate)
			//		end
			//		
			//		if @Old_ToDate is null and @ThroughDate is not null
			//		begin
			//			insert into T select %olddata%, @ThroughDate + 1 FromDate from T where %keycondition% and FromDate = @Old_FromDate
			//		end
			//
			//		if @AsOf <= @Old_FromDate
			//		begin
			//			if @AsOf < @Old_FromDate
			//			begin
			//				delete T where %keycondition% and FromDate >= @AsOf and ToDate < @Old_FromDate
			//				update T set ToDate = @AsOf - 1 where %keycondition% and FromDate = (select Max(FromDate) from T where %keycondition% and FromDate < @AsOf)
			//			end
			//	%EndIsTemporal%
			//			delete T where %keycondition% and FromDate = @Old_FromDate and %concurrencycondition%
			//			set @RowsAffected = @@ROWCOUNT
			//	%IsTemporal%
			//		end
			//		else
			//		begin
			//			update T set ToDate = @AsOf - 1 where %keycondition% and FromDate = @Old_FromDate and %concurrencycondition%
			//			set @RowsAffected = @@ROWCOUNT
			//		end
			//	%EndIsTemporal%
			
			var insertList = entityType.ColumnTypes.Where(c => c.IsStored && c.Name != serverEntityType.IdentityColumnName);
			
			// TODO: Temporal granularity { Day, Hour, Minute, Second, Millisecond }
			if (entityType.IsTemporal)
			{
				statement.AppendFormat
				(
					"if @Old_{3} is not null and (@{3} is null or @{3} <> @Old_{3})\r\n" +
					"begin\r\n" +
					"\tif @{3} is not null\r\n" +
					"\t\tupdate {0} set {2} = DateAdd(day, 1, @{3})\r\n" +
					"\t\t\twhere {1} and {2} = (select Max({2}) from {0} where {1} and {2} <= @{3})\r\n" +
					"\tdelete {0} where {1} and {2} > @Old_{3} and (@{3} is null or {3} < @{3})\r\n" +
					"end\r\n" +
					"\r\n" +
					"if @Old_{3} is null and @{3} is not null\r\n" +
					"begin\r\n" +
					"\tinsert\r\n" +
					"\t\tinto {0} ({4})\r\n" +
					"\t\tselect {5} from {0} where {1} and {2} = @Old_{2}\r\n" +
					"end\r\n" +
					"\r\n" +
					"if @{2} <= @Old_{2}\r\n" +
					"begin\r\n" +
					"\tif @{2} < @Old_{2}\r\n" +
					"\tbegin\r\n" +
					"\t\tdelete {0} where {1} and {2} >= @{2} and {3} < @Old_{2}\r\n" +
					"\t\tupdate {0} set {3} = DateAdd(day, -1, @{2}) where {1} and {2} = (select Max({2}) from {0} where {1} and {2} < @{2})\r\n" +
					"\tend\r\n",
					GetEntityFullName(serverEntityType),
					keyCondition,
					entityType.FromDateColumnName,
					entityType.ToDateColumnName,
					insertList.Select(c => c.Name).Combine(", "),
					insertList.Select(c => c.Name == entityType.FromDateColumnName ? "DateAdd(day, 1, @" + entityType.ToDateColumnName + ")" : c.Name).Combine(", ")
				);
			}
			
			statement.AppendFormat
			(
				"\tdelete {0} where {1}{2}{3}\r\n" +
				"\tset @RowsAffected = @@ROWCOUNT\r\n",
				GetEntityFullName(serverEntityType),
				keyCondition,
				entityType.IsTemporal ? String.Format(" and {0} = @Old_{0}", entityType.FromDateColumnName) : "",
				concurrencyCondition.Length > 0 ? " and " + concurrencyCondition : ""
			);
				
			if (entityType.IsTemporal)
			{
				statement.AppendFormat
				(
					"end\r\n" +
					"else\r\n" +
					"begin\r\n" +
					"\tupdate {0} set {3} = DateAdd(day, -1, @{2}) where {1} and {2} = @Old_{2}{4}\r\n" +
					"\tset @RowsAffected = @@ROWCOUNT\r\n" +
					"end\r\n",
					GetEntityFullName(serverEntityType),
					keyCondition,
					entityType.FromDateColumnName,
					entityType.ToDateColumnName,
					concurrencyCondition.Length > 0 ? " and " + concurrencyCondition : ""
				);
			}

			commandDef.Statement = statement.ToString();
		}

		protected virtual void InferServerEntityType(ServerEntityType serverEntityType, EntityType entityType)
		{
			if (!serverEntityType.HasSelectCommand())
				InferSelectCommand(serverEntityType, entityType);
				
			if (serverEntityType.SupportsInsert && !serverEntityType.HasInsertCommand())
				InferInsertCommand(serverEntityType, entityType);
				
			if (serverEntityType.SupportsUpdate && !serverEntityType.HasUpdateCommand())
				InferUpdateCommand(serverEntityType, entityType);
				
			if (serverEntityType.SupportsDelete && !serverEntityType.HasDeleteCommand())
				InferDeleteCommand(serverEntityType, entityType);
		}
		
		private EntityTypeCacheItem InternalGet(string entityTypeName)
		{
			EntityTypeCacheItem cacheItem = null;
			lock (_entityTypeCacheSync)
			{
				if (!_entityTypeCache.TryGetValue(entityTypeName, out cacheItem))
				{
					cacheItem = new EntityTypeCacheItem(entityTypeName);
					_entityTypeCache.Add(entityTypeName, cacheItem);
					try
					{
						cacheItem.EntityType = EntityTypeFactory.Create(entityTypeName);
						cacheItem.ServerEntityType = ServerEntityTypeFactory.Create(entityTypeName);
						
						if (!cacheItem.ServerEntityType.HasSelectCommand())
						{
							InferEntityTypeFromStructure(cacheItem.ServerEntityType, cacheItem.EntityType);
						}
						else
						{
							InferEntityTypeFromSchemaTable
							(
								cacheItem.ServerEntityType,
								cacheItem.EntityType,
								cacheItem.ServerEntityType.SelectCommand.CommandType == ServerEntityCommandType.Statement	
									? Database.DescribeStatement(cacheItem.ServerEntityType.SelectStatement)
									: Database.DescribeProcedure(cacheItem.ServerEntityType.SelectProcedure)
							);
						}
						
						InferServerEntityType(cacheItem.ServerEntityType, cacheItem.EntityType);
						
						cacheItem.EntityType.FinishInitialization();
						cacheItem.ServerEntityType.FinishInitialization();
					}
					catch
					{
						_entityTypeCache.Remove(entityTypeName);
						throw;
					}
				}
				
				return cacheItem;
			}
		}
		
		#region IEntityTypeProvider methods

		/// <inheritdoc/>
		public EntityType GetEntityType(string entityTypeName)
		{
			return InternalGet(entityTypeName).EntityType;
		}
		
		#endregion
		
		#region IServerEntityTypeProvider methods
		
		/// <inheritdoc/>
		public ServerEntityType GetServerEntityType(string entityTypeName)
		{
			return InternalGet(entityTypeName).ServerEntityType;
		}

		/// <inheritdoc/>
        public virtual void EnsureEntityTypes(IEnumerable<string> entityTypeNames)
        {
            // Do nothing for server implementation
        }

		#endregion
	}
}
